﻿using HostAssistantCore;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace HostAssistant
{
    public partial class addsqldb : U1Form
    {
        public addsqldb()
        {
            InitializeComponent();
            lblhid.Visible = false;
        }
        #region 添加mssql

        private void simpleButton1_Click(object sender, EventArgs e)
        {
          
            try
            {

                if (String.IsNullOrWhiteSpace(txtuser.Text))
                {
                    DevExpress.XtraEditors.XtraMessageBox.Show("数据库名不能为空", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }
                if (String.IsNullOrWhiteSpace(txtpwd.Text))
                {
                    DevExpress.XtraEditors.XtraMessageBox.Show("数据库密码不能为空", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }
                Regex r = new Regex(@"[a-zA-Z](?=.*?[1-9].*?)[a-zA-Z1-9]+|[1-9](?=.*?[a-zA-Z].*?)[a-zA-Z1-9]+");
                if (!r.IsMatch(txtpwd.Text.Trim()))
                {
                    DevExpress.XtraEditors.XtraMessageBox.Show("数据库密码格式错误", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }
                Regex r2 = new Regex(@"((?=[\x21-\x7e]+)[^A-Za-z0-9])");
                if (!r2.IsMatch(txtpwd.Text.Trim()))
                {
                    DevExpress.XtraEditors.XtraMessageBox.Show("数据库密码格式错误", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }
                if (txtpwd.Text.Trim().Length < 8)
                {
                    DevExpress.XtraEditors.XtraMessageBox.Show("数据库密码格式错误", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }
                
                OpHelper.CallWithTimeout(Addmssql, 5000);

            }
            catch (Exception ex)
            {
                NLogger.Error(ex.ToString());

                try
                {
                    string password = "";
                    string port = "";
                    var set = Data<HostAssistantCore.setting>.GetInstance().GetModelList("select *  from setting", null);
                    var mysql = set.FirstOrDefault(o => o.type == "mssql");



                    string ip = mysql?.ip ?? "";
                    port = mysql?.port ?? "";
                    string user = mysql?.username ?? "";
                    password = mysql?.userpwd ?? "";

                    SqlDbHelper.Initialize("master", user, password);

                    SqlDbHelper.ExecuteNormalSql("DROP DATABASE [" + txtuser.Text + "]");

                    SqlDbHelper.ExecuteNormalSql("DROP LOGIN [" + txtuser.Text + "]");
                }
                catch (Exception exc)
                {
                    NLogger.Error(exc.ToString());
                }


                    splashScreenManager1.CloseWaitForm();
                DevExpress.XtraEditors.XtraMessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            finally
            {
                if (lblhid.Text == "1")
                {
                    lblhid.Text = "0";
                    Form1 f1 = (Form1)this.Owner;
                    f1.op("数据库列表");
                    this.Dispose();
                }
              
            }

        }


         void Addmssql()
        {
            splashScreenManager1.ShowWaitForm();
            splashScreenManager1.SetWaitFormCaption("请等待");
            splashScreenManager1.SetWaitFormDescription("加载中...");
            string password = "";
            string port = "";
            var set = Data<HostAssistantCore.setting>.GetInstance().GetModelList("select *  from setting", null);
            var mysql = set.FirstOrDefault(o => o.type == "mssql");
            if (mysql == null)
            {
                splashScreenManager1.CloseWaitForm();
                DevExpress.XtraEditors.XtraMessageBox.Show("请先到“系统配置”进行相关参数配置", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;

            }


            string ip = mysql?.ip ?? "";
            port = mysql?.port ?? "";
            string user = mysql?.username ?? "";
            password = mysql?.userpwd ?? "";
            SqlInfo mysqlinfo = new SqlInfo();
            mysqlinfo.Mdbname = this.txtuser.Text.Trim().ToString();//数据库名
            mysqlinfo.Muser = this.txtuser.Text.Trim().ToString();//用户名
            mysqlinfo.Mpass = this.txtpwd.Text.Trim().ToString();//密码
            mysqlinfo.sqlsize = ZConvert.StrToInt(this.txtkj.Text.Trim().ToString());//空间
            mysqlinfo.Mtime = DateTime.Now.ToString();//时间
            SqlDbHelper.Initialize("master", user, password);
            if (SqlDbHelper.ConnectionTest())
            {
                DataSet ds_mysql_user = SqlDbHelper.Query("select name from syslogins", null);
                if (ds_mysql_user.Tables[0].Rows.Count != 0)
                {
                  
                    DataTable dt_users = ds_mysql_user.Tables[0];
               
                    foreach (DataRow dr in dt_users.Rows)
                    {
                        if (dr["name"].ToString() == mysqlinfo.Mdbname)
                        {
                            splashScreenManager1.CloseWaitForm();
                            DevExpress.XtraEditors.XtraMessageBox.Show("数据库用户已经存在", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

                            return;

                        }
                    }
                }
                   
                DataSet ds_mysql_dbname = SqlDbHelper.Query("Select name from master..sysdatabases", null);
                if (ds_mysql_dbname.Tables[0].Rows.Count != 0)
                {
                    DataTable dt_dbnames = ds_mysql_dbname.Tables[0];
                    foreach (DataRow dr in dt_dbnames.Rows)
                    {
                        if (dr["name"].ToString() == mysqlinfo.Mdbname)
                        {
                            splashScreenManager1.CloseWaitForm();
                            DevExpress.XtraEditors.XtraMessageBox.Show("数据库已经存在", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                            return;

                        }
                    }
                }

              
                string mssqldatapath = mysql.Path;

             
                if (!System.IO.Directory.Exists(mssqldatapath + "\\" + mysqlinfo.Mdbname))
                {
                    try
                    {
                        System.IO.Directory.CreateDirectory(mssqldatapath + "\\" + mysqlinfo.Mdbname);

                    }
                    catch (Exception ex)
                    {

                        NLogger.Error("创建sql :" + ex.ToString());
                        splashScreenManager1.CloseWaitForm();
                        DevExpress.XtraEditors.XtraMessageBox.Show("数据库存放路径创建失败", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

                        return;


                    }
                }
                
                if (!SqlDbHelper.ExecuteNormalSql("create database " + mysqlinfo.Mdbname + " on(name=" + mysqlinfo.Mdbname + "_data,filename=\"" + mssqldatapath + "\\" + mysqlinfo.Mdbname + "\\" + mysqlinfo.Mdbname + "_data.mdf\")log on(name=" + mysqlinfo.Mdbname + "_log,filename=\"" + mssqldatapath + "\\" + mysqlinfo.Mdbname + "\\" + mysqlinfo.Mdbname + "_log.ldf\")"))//添加库
                {  
                  
                    splashScreenManager1.CloseWaitForm();
                    DevExpress.XtraEditors.XtraMessageBox.Show("添加数据库'" + mysqlinfo.Mdbname + "'失败!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

                    return;

                }
            


                if (mysqlinfo.sqlsize > 0)
                {
                    if (!SqlDbHelper.ExecuteNormalSql("ALTER DATABASE " + mysqlinfo.Mdbname + " MODIFY FILE(name=" + mysqlinfo.Mdbname + "_data,MAXSIZE=" + mysqlinfo.sqlsize + "mb)"))
                    {
                       
                        NLogger.Info("未能成功设置限制大小");
                    }

                }
                
                if (!SqlDbHelper.ExecuteNormalSql("create login " + mysqlinfo.Mdbname + " with password='" + mysqlinfo.Mpass + "', default_database=" + mysqlinfo.Mdbname))
                {
                  
                    splashScreenManager1.CloseWaitForm();
                    DevExpress.XtraEditors.XtraMessageBox.Show("创建登录用户失败", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

                    return;


                }
               
                if (!SqlDbHelper.ExecuteNormalSql("create user " + mysqlinfo.Mdbname + " for login " + mysqlinfo.Mdbname + " with default_schema=" + mysqlinfo.Mdbname))
                {

                    SqlDbHelper.ExecuteNormalSql("drop login " + mysqlinfo.Mdbname);
                 
                    splashScreenManager1.CloseWaitForm();
                    DevExpress.XtraEditors.XtraMessageBox.Show("创建数据库用户失败", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

                    return;

                }
             
                if (!SqlDbHelper.ExecuteNormalSql("exec sp_addrolemember 'db_owner','" + mysqlinfo.Mdbname + "'"))
                {

                    SqlDbHelper.ExecuteNormalSql("drop login " + mysqlinfo.Mdbname);
                    SqlDbHelper.ExecuteNormalSql("drop user " + mysqlinfo.Mdbname);
              
                    splashScreenManager1.CloseWaitForm();
                    DevExpress.XtraEditors.XtraMessageBox.Show("赋予用户数据库权限失败", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

                    return;


                }



                string sql = " USE [" + mysqlinfo.Mdbname + "] ";
                sql += "      ";
                sql += "  CREATE USER [" + mysqlinfo.Mdbname + "] FOR LOGIN [" + mysqlinfo.Mdbname + "] ";
                sql += "   ";
                sql += "  USE [" + mysqlinfo.Mdbname + "] ";
                sql += "   ";
                sql += "  ALTER USER [" + mysqlinfo.Mdbname + "] WITH DEFAULT_SCHEMA =[dbo] ";
                sql += "   ";
                sql += "  USE [" + mysqlinfo.Mdbname + "] ";
                sql += "   ";
                sql += "  EXEC sp_addrolemember N'db_owner', N'" + mysqlinfo.Mdbname + "' ";
                sql += "   ";
                sql += "  USE [master] ";
                sql += "   ";
                /****** Object:  User [conan002]    Script Date: 01/30/2018 18:23:22 ******/
                sql += " DROP USER [" + mysqlinfo.Mdbname + "] ";
                sql += "  ";

                if (!SqlDbHelper.ExecuteNormalSql(sql))
                {


                    SqlDbHelper.ExecuteNormalSql("drop login " + mysqlinfo.Mdbname);
                    SqlDbHelper.ExecuteNormalSql("drop user " + mysqlinfo.Mdbname);
                   


                    splashScreenManager1.CloseWaitForm();
                    DevExpress.XtraEditors.XtraMessageBox.Show("赋予用户数据库权限失败", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

                    return;

                }




                string get_schema_name_sql = "Select name,crdate,filename from master..sysdatabases where name='" + mysqlinfo.Mdbname + "'";
                DataSet ds_schema_name = SqlDbHelper.Query(get_schema_name_sql, null);//执行语句
                if (ds_schema_name.Tables[0].Rows.Count != 0)
                {
                    DataRow dbrow = ds_schema_name.Tables[0].Rows[0];
                    mysqlinfo.Mpath = dbrow["filename"].ToString();//路径
                    mysqlinfo.Mtime = dbrow["crdate"].ToString();//时间
                }


               // mysqlinfo.Mpath = mssqldatapath;

                string get_collate_sql = "SELECT COLLATIONPROPERTY('Chinese_PRC_Stroke_CI_AI_KS_WS', 'CodePage') as 'collate'";
                DataSet ds_collate = SqlDbHelper.Query(get_collate_sql, null);//执行语句
                if (ds_collate.Tables[0].Rows.Count != 0)
                {
                    string collate = ds_collate.Tables[0].Rows[0]["collate"].ToString();
                    if (collate == "936")
                    {
                        mysqlinfo.Municode = "简体中文GBK";
                    }
                    if (collate == "950") { mysqlinfo.Municode = "繁体中文BIG5"; }
                    if (collate == "437") { mysqlinfo.Municode = "美国/加拿大英语"; }
                    if (collate == "932") { mysqlinfo.Municode = "日文"; }
                    if (collate == "949") { mysqlinfo.Municode = "韩文"; }
                    if (collate == "866") { mysqlinfo.Municode = "俄文"; }
                    if (collate == "65001") { mysqlinfo.Municode = "unicode UFT-8"; }
                }

                mysqlinfo.type = "mssql";
                string query = "INSERT INTO SqlInfo( Mpath, Municode, sqlsize, type,Mdbname,Muser,Mpass,Mtime)VALUES(  @Mpath, @Municode, @sqlsize,@type,@Mdbname,@Muser,@Mpass,@Mtime)   ";

                Data<SqlInfo>.GetInstance().Add(query, mysqlinfo);
                splashScreenManager1.CloseWaitForm();
                DevExpress.XtraEditors.XtraMessageBox.Show("成功添加数据库", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                
            }
            else
            {
                splashScreenManager1.CloseWaitForm();
                DevExpress.XtraEditors.XtraMessageBox.Show("未能连接上数据库,请检查数据库账号密码!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            lblhid.Text = "1";
        }

        #endregion

        #region 返回
        private void simpleButton2_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        #endregion

        /// <summary>
        /// 数字
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void txtkj_KeyPress(object sender, KeyPressEventArgs e)
        {
            e.Handled = true;
            if (e.KeyChar >= '0' && e.KeyChar <= '9')
            {
                e.Handled = false;
            }
        }
    }
}
